package com.ustcinfo.study.hive.r1.chenzhiyu;

import java.sql.*;

/**
 * 将结构化文本数据导入hive，并查询
 * 新建表与插数据语句被注释，第一次运行请取消注释
 * @Author chen.zhiyu2
 * @Mail: chen.zhiyu2@ustcinfo.com/chenzhiyuml@foxmail.com
 * @Date 2018/08/03
 */
public class HiveConnection {

    // hive 地址
    private static String url = "jdbc:hive2://nodex100:10000/default";

    //hive 用户名
    private static String userName = "hive";

    // hive 密码
    private static String passwd = "hive";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询 tables sql
    private static String showTableSql = "show tables";

    // 创建内部表 member sql
    private static String createSql =
            "create table member(ip string,domain_name string,access_time string,target_ip string,status string)"+
                    "row format delimited fields terminated by '#' ";

    // 加载数据到 member sql
    private static String localDataSql = "load data local inpath '/home/ustc/dnsSample' into table member ";

    // 查看表中的所有数据
    private static String selectSql = "select * from member";

    // 统计出 member 表中 domain_name 个数
    private static String countSql = "select count(distinct domain_name) from member";

    public static void main(String[] args) throws SQLException {

        //  加载hive驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }

        try {
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }

            statement = connection.createStatement();

            // 获取数据库中所有的表
            resultSet = statement.executeQuery(showTableSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
            }
                //新建数据库表
//            statement.execute(createSql);
                //把文本数据导入Hive
//            statement.execute(localDataSql);
            //查询表中数据并打印
            resultSet = statement.executeQuery(selectSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2)+
                        "\t" + resultSet.getString(3)+"\t" + resultSet.getString(4)+
                        "\t" + resultSet.getString(5));
            }

            //统计域名数，并打印
            resultSet = statement.executeQuery(countSql);
            while (resultSet.next()){
                System.out.println(resultSet.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
            if(connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }
}
